﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using DBLibrary;

public static class PoseidonDBHelper
{
    private static object mSyncObject;
    static PoseidonDBHelper()
    {
        mSyncObject = new object();
    }
    public static DB UserDB
    {
        get
        {
            lock (mSyncObject)
            {
                InternalDB idb = new InternalDB(new UserConnectionConfig());
                return idb;
            }
        }
    }
    public static DB AdminDB
    {
        get
        {
            lock (mSyncObject)
            {
                InternalDB idb = new InternalDB(new AdminConnectionConfig());
                return idb;
            }
        }
    }

    #region sql schema
    private static string mSQLAddDBSchema = @"
    DECLARE @DefaultData VARCHAR(100) 
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key='Software\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\Setup', 
@value_name='SQLDataRoot', 
@DefaultData=@DefaultData OUTPUT 
set @DefaultData =@DefaultData+'\data\'
declare @createDBSql varchar(8000) ='
CREATE DATABASE [PoseidonDB_{0}] ON  PRIMARY 
( NAME = ''PoseidonDB_{0}'', FILENAME = '''+@DefaultData+'PoseidonDB_{0}.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = ''PoseidonDB_{0}_log'', FILENAME = '''+@DefaultData+'PoseidonDB_{0}_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
'
exec (@createDBSql)
";
    private static string mKeyFileSchema = @"
create table [KeyFile]
(
KeyId varchar(160) primary key not null ,
Content nvarchar(100) null,
IsInUse bit not null,
[Type] int not null,
DBPre varchar(100) null,
[IsDeleted] bit not null default('false'),
)";
    private static string mCustomerLevelSchema = @"
create table [CustomerLevel]
(
LevelId int identity primary key not null,
LevelName nvarchar(max) not null,
[IsDeleted] bit not null default('false'),
)";
    private static string mCustomerSchema = @"
create table [Customer]
(
[CustomerId] bigint identity primary key not null,
[Name] nvarchar(20) not null,
[EMail] nvarchar(100) null,
[Level] int null,
[Gender] nvarchar(2) null,
[Phone] varchar(20) null,
[Extend] nvarchar(max) null,
[IsDeleted] bit not null default('false'),
constraint FK_CustomerLevel_LevelID foreign key([Level]) references [CustomerLevel]([LevelId]),
)";
    private static string mCustomerExtendSchema = @"
create table [CustomerExtend]
(
[CustomerId] bigint not null,
[Row] int not null,
[str1] nvarchar(max) null,
[str2] nvarchar(max) null,
[str3] nvarchar(max) null,
[str4] nvarchar(max) null,
[str5] nvarchar(max) null,
[str6] nvarchar(max) null,
[IsDeleted] bit not null default('false'),
constraint FK_Customer_CustomerId FOREIGN KEY(CustomerId) REFERENCES Customer(CustomerId),
)";

    private static string mSpendTypeSchema = @"
create table [SpendType]
(
[SpendTypeId] bigint identity primary key not null,
[SpendTypeName] nvarchar(100) not null,
[IsDeleted] bit not null default('false'),
)";
    private static string mSpendItemSchema = @"
create table [SpendItem]
(
[SpendItemId] bigint identity primary key not null,
[SpendItemName] nvarchar(100) not null,
[SpendItemPrice] float not null,
[SpendTypeId] bigint not null,
[IsDeleted] bit not null default('false'),
constraint FK_SpendType_SpendTypeId foreign key(SpendTypeId) references [SpendType]([SpendTypeId])
)";
    private static string mBillSchema = @"
create table [Bill]
(
[BillId] bigint identity primary key not null,
[CustomerName] nvarchar(20) not null,
[SpendItemName] nvarchar(100) not null,
[SpendItemPrice] float not null,
[SpendTypeName] nvarchar(100) not null,
[SpendItemCount] int not null,
[Time] datetime not null,
[IsDeleted] bit not null default('false'),
)";

    #endregion

    public static void CreateNewUserDB(string dbpre)
    {
        string dbSql = string.Format(mSQLAddDBSchema, dbpre);
        InternalDB idb = (InternalDB)AdminDB;
        idb.CreateNewEmptyUserDB(dbSql);
        InternalDB db = new InternalDB(new UserConnectionConfig(dbpre));
        db.CreateNewEmptyUserDB(mKeyFileSchema);
        db.CreateNewEmptyUserDB(mCustomerLevelSchema);
        db.CreateNewEmptyUserDB(mCustomerSchema);
        db.CreateNewEmptyUserDB(mCustomerExtendSchema);
        db.CreateNewEmptyUserDB(mSpendTypeSchema);
        db.CreateNewEmptyUserDB(mSpendItemSchema);
        db.CreateNewEmptyUserDB(mBillSchema);
    }
}

class InternalDB : DBLibrary.DB
{
    public InternalDB(IConnectionConfiguration conf)
        : base(conf)
    {
        mID = Guid.NewGuid();
    }

    private Guid mID;
    public Guid ID { get { return mID; } }

    public int CreateNewEmptyUserDB(string sql)
    {
        return (int)base.Excute(sql);
    }

}